package com.tuanzi.loan.business.repository.statistics.impl;

import com.tuanzi.loan.business.entity.family.ProductInfo;
import com.tuanzi.loan.business.repository.statistics.custom.NewOneKeyStatRepositoryCustom;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.statistics.newonekey.NewOneKeyStatRequest;
import com.tuanzi.loan.vo.request.statistics.novice.NoviceStatRequest;
import com.tuanzi.loan.vo.response.statistics.newonekey.NewOneKeyStatDetailResponse;
import com.tuanzi.loan.vo.response.statistics.newonekey.NewOneKeyStatTotalResponse;
import com.tuanzi.loan.vo.response.statistics.novice.NoviceDetailStatResponse;
import com.tuanzi.loan.vo.response.statistics.novice.NoviceTotalStatResponse;
import org.apache.commons.lang3.StringUtils;
import org.jooq.*;
import org.jooq.impl.DSL;
import org.jooq.impl.SQLDataType;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import java.sql.Date;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import static com.tuanzi.loan.generated.jooq.loan.tables.CommProductChannel.COMM_PRODUCT_CHANNEL;
import static com.tuanzi.loan.generated.jooq.loan.tables.CommProductPrdid.COMM_PRODUCT_PRDID;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanAdvert.LOAN_ADVERT;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanApiRecord.LOAN_API_RECORD;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanOrder.LOAN_ORDER;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanProductInfo.LOAN_PRODUCT_INFO;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanReceiptOrder.LOAN_RECEIPT_ORDER;
import static com.tuanzi.loan.generated.jooq.stat.tables.StatNewOnekeyUv.STAT_NEW_ONEKEY_UV;

/**
 * @author caiwanlin
 * @create 2018-06-27 16:31
 */
public class NewOneKeyStatRepositoryImpl extends JOOQBaseRepositoryPlus implements NewOneKeyStatRepositoryCustom {
    @Override
    protected TableImpl getTable() {
        return STAT_NEW_ONEKEY_UV;
    }

    @Override
    public Page<NewOneKeyStatTotalResponse> totalList(NewOneKeyStatRequest newOneKeyStatRequest, Pageable pageable) {
        java.util.Date date1 = newOneKeyStatRequest.getStartTime();
        java.util.Date date2 = newOneKeyStatRequest.getEndTime();
        Date startTime = new Date(date1.getTime());
        Date endTime = new Date(date2.getTime());
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String startTimeStr = format.format(date1) + " 00:00:00";
        String endTimeStr = format.format(date2)+" 23:59:59";

        Select subSelect1 = dslContext.select(DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("access_uv"), STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"))
                .from(STAT_NEW_ONEKEY_UV)
                .where(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime)
                        .and(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("New_onekeyINFO")))
                .groupBy(STAT_NEW_ONEKEY_UV.CREATE_DATE);

        Select subSelect2 = dslContext.select(DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("next_uv"), STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"))
                .from(STAT_NEW_ONEKEY_UV)
                .where(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime)
                        .and(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("Next_step")))
                .groupBy(STAT_NEW_ONEKEY_UV.CREATE_DATE);

        Select subSelect3 = dslContext.select(DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("jump_uv"), STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"))
                .from(STAT_NEW_ONEKEY_UV)
                .where(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime)
                        .and(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("Next_jump")))
                .groupBy(STAT_NEW_ONEKEY_UV.CREATE_DATE);

        Select subUnion = dslContext.select(DSL.count(LOAN_API_RECORD.ID).as("union_count"), date(LOAN_API_RECORD.CTIME).as("create_date"))
                .from(LOAN_API_RECORD)
                .where(LOAN_API_RECORD.CTIME.between(Timestamp.valueOf(startTimeStr),Timestamp.valueOf(endTimeStr))
                        .and(LOAN_API_RECORD.RESULT.eq(1))
                        .and(LOAN_API_RECORD.FIRST_UNION.eq(1))
                        .and(LOAN_API_RECORD.ORDER_FROM.eq("9")))
                .groupBy(date(LOAN_API_RECORD.CTIME));

        Select subApi = dslContext.select(DSL.count(LOAN_ORDER.ID).as("api_count"), date(LOAN_ORDER.CTIME).as("create_date"))
                .from(LOAN_ORDER)
                .where(LOAN_ORDER.ORDER_TYPE.eq(3)
                        .and(LOAN_ORDER.CTIME.between(Timestamp.valueOf(startTimeStr),Timestamp.valueOf(endTimeStr))))
                .groupBy(date(LOAN_ORDER.CTIME));

        Select subReceipt = dslContext.select(DSL.count(LOAN_RECEIPT_ORDER.ID).as("receipt_count"), date(LOAN_RECEIPT_ORDER.COMMIT_TIME).as("create_date"))
                .from(LOAN_RECEIPT_ORDER)
                .where(LOAN_RECEIPT_ORDER.SOURCE_FROM.eq(10)
                        .and(LOAN_RECEIPT_ORDER.COMMIT_TIME.between(Timestamp.valueOf(startTimeStr),Timestamp.valueOf(endTimeStr))))
                .groupBy(date(LOAN_RECEIPT_ORDER.COMMIT_TIME));

        Select subH5 = dslContext.select(DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("h_uv"), STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"))
                .from(STAT_NEW_ONEKEY_UV)
                .where(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime)
                        .and(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("Next_jump"))
                        .and(STAT_NEW_ONEKEY_UV.PARTY_TYPE.eq(1)))
                .groupBy(STAT_NEW_ONEKEY_UV.CREATE_DATE);

        SelectSeekStep1 totalSelect = dslContext.select(subSelect1.field("create_date"),
                subSelect1.field("access_uv"),
                subSelect2.field("next_uv"),
                subSelect3.field("jump_uv"),
                subUnion.field("union_count"),
                subApi.field("api_count"),
                subReceipt.field("receipt_count"),
                subH5.field("h_uv")
        )
                .from(subSelect1)
                .leftJoin(subSelect2).on(subSelect1.field("create_date").eq(subSelect2.field("create_date")))
                .leftJoin(subSelect3).on(subSelect1.field("create_date").eq(subSelect3.field("create_date")))
                .leftJoin(subUnion).on(subSelect1.field("create_date").eq(subUnion.field("create_date")))
                .leftJoin(subApi).on(subSelect1.field("create_date").eq(subApi.field("create_date")))
                .leftJoin(subReceipt).on(subSelect1.field("create_date").eq(subReceipt.field("create_date")))
                .leftJoin(subH5).on(subSelect1.field("create_date").eq(subH5.field("create_date")))
                .orderBy(subSelect1.field("create_date").asc());
        Long count = dslContext.selectCount().from(totalSelect).fetchOneInto(Long.class);
        List content = totalSelect.offset(pageable.getOffset()).limit(pageable.getPageSize())
                .fetch(new TotalRecordMapper());

        return new PageImpl<NewOneKeyStatTotalResponse>(content, pageable, count);
    }

    class TotalRecordMapper implements RecordMapper<Record, NewOneKeyStatTotalResponse> {

        @Override
        public NewOneKeyStatTotalResponse map(Record record) {
            NewOneKeyStatTotalResponse response = new NewOneKeyStatTotalResponse();
            Integer accessUv = record.get("access_uv", Integer.class);
            Integer nextUv = record.get("next_uv", Integer.class);
            Integer jumpUv = record.get("jump_uv", Integer.class);
            Integer unionCount = record.get("union_count", Integer.class);
            Integer apiCount = record.get("api_count", Integer.class);
            Integer receiptCount = record.get("receipt_count", Integer.class);
            Integer hUv = record.get("h_uv", Integer.class);
            response.setCreateDate(record.get("create_date", java.util.Date.class));
            response.setAccessUv(accessUv == null ? 0 : accessUv);
            response.setNextUv(nextUv == null ? 0 : nextUv);
            response.setJumpUv(jumpUv == null ? 0 : jumpUv);
            response.setOrderCount((unionCount == null ? 0 : unionCount) + (apiCount == null ? 0 : apiCount) +
                    (receiptCount == null ? 0 : receiptCount) + (hUv == null ? 0 : hUv));

            return response;
        }
    }

    @Override
    public Page<NewOneKeyStatDetailResponse> detailList(NewOneKeyStatRequest newOneKeyStatRequest, Pageable pageable) {
        java.util.Date date1 = newOneKeyStatRequest.getStartTime();
        java.util.Date date2 = newOneKeyStatRequest.getEndTime();
        Date startTime = new Date(date1.getTime());
        Date endTime = new Date(date2.getTime());
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String startTimeStr = format.format(date1)+" 00:00:00";
        String endTimeStr = format.format(date2) +" 23:59:59";
        Timestamp startTimeStamp = Timestamp.valueOf(startTimeStr);
        Timestamp endTimeStamp = Timestamp.valueOf(endTimeStr);
        Integer productId = newOneKeyStatRequest.getProductId();
        Integer channel = newOneKeyStatRequest.getChannel();
        String platform = newOneKeyStatRequest.getPlatform();
        Integer partyType = newOneKeyStatRequest.getPartyType();
        Long activityId = newOneKeyStatRequest.getActivityId();
        List<Condition> unionConditionList = new ArrayList<>();
        List<Condition> apiConditionList = new ArrayList<>();
        List<Condition> receiptConditionList = new ArrayList<>();
        List<Condition> statConditionList = new ArrayList<>();
        //联登查询条件
        unionConditionList.add(LOAN_API_RECORD.CTIME.between(startTimeStamp, endTimeStamp));
        unionConditionList.add(LOAN_API_RECORD.RESULT.eq(1));
        unionConditionList.add(LOAN_API_RECORD.FIRST_UNION.eq(1));
        unionConditionList.add(LOAN_API_RECORD.ORDER_FROM.eq("9"));
        //api查询条件
        apiConditionList.add(LOAN_ORDER.ORDER_TYPE.eq(3));
        apiConditionList.add(LOAN_ORDER.CTIME.between(startTimeStamp, endTimeStamp));
        //借条查询条件
        receiptConditionList.add(LOAN_RECEIPT_ORDER.SOURCE_FROM.eq(10));
        receiptConditionList.add(LOAN_RECEIPT_ORDER.COMMIT_TIME.between(startTimeStamp, endTimeStamp));

        //查询主表的条件
        statConditionList.add(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("Next_jump"));
        statConditionList.add(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime));
        if (productId != null) {
            unionConditionList.add(LOAN_API_RECORD.PRODUCT_ID.eq(productId.longValue()));
            apiConditionList.add(LOAN_ORDER.PRODUCT_ID.eq(String.valueOf(productId)));
            statConditionList.add(STAT_NEW_ONEKEY_UV.PRODUCT_ID.eq(productId));
        }
        if (channel != null) {
            unionConditionList.add(LOAN_API_RECORD.CHANNEL.eq(channel.longValue()));
            apiConditionList.add(LOAN_ORDER.CHANNEL.eq(channel));
            statConditionList.add(STAT_NEW_ONEKEY_UV.CHANNEL.eq(channel));
        }
        if (StringUtils.isNotBlank(platform)) {
            unionConditionList.add(LOAN_API_RECORD.PLATFORM.eq(platform));
            apiConditionList.add(LOAN_ORDER.PLATFORM.eq(platform));
            statConditionList.add(STAT_NEW_ONEKEY_UV.PLATFORM.eq(platform));
        }
        if (partyType != null) {
            statConditionList.add(STAT_NEW_ONEKEY_UV.PARTY_TYPE.eq(partyType));
        }
        if (activityId != null) {
            statConditionList.add(STAT_NEW_ONEKEY_UV.ACTIVITY_ID.eq(activityId));
        }

        Select subUnion = dslContext.select(DSL.count(LOAN_API_RECORD.ID).as("union_count"), LOAN_API_RECORD.SOURCE_ID.as("source_id"),
                date(LOAN_API_RECORD.CTIME).as("create_date")).from(LOAN_API_RECORD)
                .where(unionConditionList).groupBy(LOAN_API_RECORD.SOURCE_ID, date(LOAN_API_RECORD.CTIME));

        Select subApi = dslContext.select(DSL.count(LOAN_ORDER.ID).as("api_count"), LOAN_ORDER.SOURCE_ID.as("source_id"),
                date(LOAN_ORDER.CTIME).as("create_date")).from(LOAN_ORDER)
                .where(apiConditionList).groupBy(LOAN_ORDER.SOURCE_ID, date(LOAN_ORDER.CTIME));

        Select subReceipt = dslContext.select(DSL.count(LOAN_RECEIPT_ORDER.ID).as("receipt_count"), LOAN_RECEIPT_ORDER.PRODUCT_ID.as("source_id"),
                date(LOAN_RECEIPT_ORDER.COMMIT_TIME).as("create_date")).from(LOAN_RECEIPT_ORDER)
                .where(receiptConditionList).groupBy(date(LOAN_RECEIPT_ORDER.COMMIT_TIME));

        //查询对应的借条产品id
        Select subReceiptId = dslContext.select(LOAN_PRODUCT_INFO.RECEIPT_PRODUCT_ID.as("receipt_product_id"))
                .from(LOAN_PRODUCT_INFO).where(LOAN_PRODUCT_INFO.ID.eq(STAT_NEW_ONEKEY_UV.ACTIVITY_ID));

        SelectSeekStep2 detailSelect = dslContext.select(STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"), STAT_NEW_ONEKEY_UV.PRODUCT_ID.as("product_id"),
                COMM_PRODUCT_PRDID.NAME.as("product_name"), STAT_NEW_ONEKEY_UV.CHANNEL.as("channel"),
                COMM_PRODUCT_CHANNEL.CHANNEL_NAME.as("channel_name"), STAT_NEW_ONEKEY_UV.PLATFORM.as("platform"),
                STAT_NEW_ONEKEY_UV.ACTIVITY_ID.as("activity_id"), STAT_NEW_ONEKEY_UV.ACTIVITY_NAME.as("activity_name"),
                STAT_NEW_ONEKEY_UV.PARTY_TYPE.as("party_type"), DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("uv"),
                subUnion.field("union_count"), subApi.field("api_count"), subReceipt.field("receipt_count"))
                .from(STAT_NEW_ONEKEY_UV)
                .leftJoin(COMM_PRODUCT_PRDID).on(COMM_PRODUCT_PRDID.PRDID.eq(STAT_NEW_ONEKEY_UV.PRODUCT_ID))
                .leftJoin(COMM_PRODUCT_CHANNEL).on(COMM_PRODUCT_CHANNEL.CHANNEL_CODE.eq(STAT_NEW_ONEKEY_UV.CHANNEL))
                .leftJoin(subUnion)
                .on(subUnion.field("source_id").eq(STAT_NEW_ONEKEY_UV.ACTIVITY_ID).and(subUnion.field("create_date").eq(STAT_NEW_ONEKEY_UV.CREATE_DATE)))
                .leftJoin(subApi)
                .on(subApi.field("source_id").eq(STAT_NEW_ONEKEY_UV.ACTIVITY_ID).and(subApi.field("create_date").eq(STAT_NEW_ONEKEY_UV.CREATE_DATE)))
                .leftJoin(subReceipt)
                .on(STAT_NEW_ONEKEY_UV.PARTY_TYPE.eq(4).and(subReceipt.field("create_date").eq(STAT_NEW_ONEKEY_UV.CREATE_DATE)))
                .where(statConditionList)
                .groupBy(STAT_NEW_ONEKEY_UV.ACTIVITY_NAME, STAT_NEW_ONEKEY_UV.CREATE_DATE)
                .orderBy(STAT_NEW_ONEKEY_UV.CREATE_DATE.asc(), STAT_NEW_ONEKEY_UV.PARTY_TYPE.asc());
        Long count = dslContext.selectCount().from(detailSelect).fetchOneInto(Long.class);
        List content = detailSelect.offset(pageable.getOffset()).limit(pageable.getPageSize()).fetch(new DetailRecordMapper());
        return new PageImpl<NewOneKeyStatDetailResponse>(content, pageable, count == null ? 0 : count);
    }


    class DetailRecordMapper implements RecordMapper<Record, NewOneKeyStatDetailResponse> {

        @Override
        public NewOneKeyStatDetailResponse map(Record record) {
            NewOneKeyStatDetailResponse response = new NewOneKeyStatDetailResponse();
            Integer productId = record.get("product_id", Integer.class);
            String productName = record.get("product_name", String.class);
            Integer channel = record.get("channel", Integer.class);
            String channelName = record.get("channel_name", String.class);
            String platform = record.get("platform", String.class);
            Long activityId = record.get("activity_id", Long.class);
            String activityName = record.get("activity_name", String.class);
            Integer partyType = record.get("party_type", Integer.class);
            Integer uv = record.get("uv", Integer.class);
            Integer unionCount = record.get("union_count", Integer.class);
            Integer apiCount = record.get("api_count", Integer.class);
            Integer receiptCount = record.get("receipt_count", Integer.class);
            response.setCreateDate(record.get("create_date", java.util.Date.class));
            response.setProductId(productId);
            response.setProductName(productName);
            response.setChannel(channel);
            response.setChannelName(channelName);
            response.setPlatform(platform);
            response.setActivityId(activityId);
            response.setActivityName(activityName);
            response.setPartyType(partyType);
            response.setInitiativeCount(uv);
            response.setOrderCount((unionCount == null ? 0 : unionCount) + (apiCount == null ? 0 : apiCount) +
                    (receiptCount == null ? 0 : receiptCount));

            return response;
        }
    }

    @Override
    public Page<NoviceTotalStatResponse> totalNoviceList(NoviceStatRequest noviceStatRequest, Pageable pageable) {
        java.util.Date date1 = noviceStatRequest.getStartTime();
        java.util.Date date2 = noviceStatRequest.getEndTime();
        Date startTime = new Date(date1.getTime());
        Date endTime = new Date(date2.getTime());
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String startTimeStr = format.format(date1) + " 00:00:00";
        String endTimeStr = format.format(date2)+" 23:59:59";

        //访问引导页uv
        Select subSelect1 = dslContext.select(DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("access_uv"), STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"))
                .from(STAT_NEW_ONEKEY_UV)
                .where(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime)
                        .and(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("view_new_welcome")))
                .groupBy(STAT_NEW_ONEKEY_UV.CREATE_DATE);
        //提交为我推荐
        Select subSelect2 = dslContext.select(DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("next_uv"), STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"))
                .from(STAT_NEW_ONEKEY_UV)
                .where(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime)
                        .and(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("to_next")))
                .groupBy(STAT_NEW_ONEKEY_UV.CREATE_DATE);
        //访问浮层引导页
        Select subSelect3 = dslContext.select(DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("guide_uv"), STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"))
                .from(STAT_NEW_ONEKEY_UV)
                .where(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime)
                        .and(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("view_guide_page")))
                .groupBy(STAT_NEW_ONEKEY_UV.CREATE_DATE);
        //点击立即投递
        Select subSelect4 = dslContext.select(DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("jump_uv"), STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"))
                .from(STAT_NEW_ONEKEY_UV)
                .where(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime)
                        .and(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("to_detail")))
                .groupBy(STAT_NEW_ONEKEY_UV.CREATE_DATE);
        //点击关闭
        Select subSelect5 = dslContext.select(DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("close_uv"), STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"))
                .from(STAT_NEW_ONEKEY_UV)
                .where(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime)
                        .and(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("to_close")))
                .groupBy(STAT_NEW_ONEKEY_UV.CREATE_DATE);

        SelectSeekStep1 totalSelect = dslContext.select(subSelect1.field("create_date"),
                subSelect1.field("access_uv"),
                subSelect2.field("next_uv"),
                subSelect3.field("guide_uv"),
                subSelect4.field("jump_uv"),
                subSelect5.field("close_uv")
        )
                .from(subSelect1)
                .leftJoin(subSelect2).on(subSelect1.field("create_date").eq(subSelect2.field("create_date")))
                .leftJoin(subSelect3).on(subSelect1.field("create_date").eq(subSelect3.field("create_date")))
                .leftJoin(subSelect4).on(subSelect1.field("create_date").eq(subSelect4.field("create_date")))
                .leftJoin(subSelect5).on(subSelect1.field("create_date").eq(subSelect5.field("create_date")))
                .orderBy(subSelect1.field("create_date").asc());

        Long count = dslContext.selectCount().from(totalSelect).fetchOneInto(Long.class);
        List content = totalSelect.offset(pageable.getOffset()).limit(pageable.getPageSize())
                .fetch(new TotalNoviceRecordMapper());

        return new PageImpl<NoviceTotalStatResponse>(content, pageable, count);
    }

    class TotalNoviceRecordMapper implements RecordMapper<Record, NoviceTotalStatResponse> {

        @Override
        public NoviceTotalStatResponse map(Record record) {
            NoviceTotalStatResponse response = new NoviceTotalStatResponse();
            Integer accessUv = record.get("access_uv", Integer.class);
            Integer nextUv = record.get("next_uv", Integer.class);
            Integer guideUv = record.get("guide_uv", Integer.class);
            Integer jumpUv = record.get("jump_uv", Integer.class);
            Integer closeUv = record.get("close_uv", Integer.class);
            response.setCreateDate(record.get("create_date", java.util.Date.class));
            response.setAccessUv(accessUv == null ? 0 : accessUv);
            response.setNextUv(nextUv == null ? 0 : nextUv);
            response.setGuideUv(guideUv == null ? 0 : guideUv);
            response.setJumpUv(jumpUv == null ? 0 : jumpUv);
            response.setCloseUv(closeUv == null ? 0 : closeUv);
            response.setOrderCount(jumpUv == null ? 0 : jumpUv);
            return response;
        }
    }


    @Override
    public Page<NoviceDetailStatResponse> detailNoviceList(NoviceStatRequest noviceStatRequest, Pageable pageable) {
        java.util.Date date1 = noviceStatRequest.getStartTime();
        java.util.Date date2 = noviceStatRequest.getEndTime();
        Date startTime = new Date(date1.getTime());
        Date endTime = new Date(date2.getTime());
        Integer productId = noviceStatRequest.getProductId();
        Integer channel = noviceStatRequest.getChannel();
        String platform = noviceStatRequest.getPlatform();
        Integer partyType = noviceStatRequest.getPartyType();
        Long activityId = noviceStatRequest.getActivityId();
        List<Condition> statConditionList = new ArrayList<>();

        //查询主表的条件
        statConditionList.add(STAT_NEW_ONEKEY_UV.CK_MODULE.eq("to_detail"));
        statConditionList.add(STAT_NEW_ONEKEY_UV.CREATE_DATE.between(startTime, endTime));
        if (productId != null) {
            statConditionList.add(STAT_NEW_ONEKEY_UV.PRODUCT_ID.eq(productId));
        }
        if (channel != null) {
            statConditionList.add(STAT_NEW_ONEKEY_UV.CHANNEL.eq(channel));
        }
        if (StringUtils.isNotBlank(platform)) {
            statConditionList.add(STAT_NEW_ONEKEY_UV.PLATFORM.eq(platform));
        }
        if (partyType != null) {
            statConditionList.add(STAT_NEW_ONEKEY_UV.PARTY_TYPE.eq(partyType));
        }
        if (activityId != null) {
            statConditionList.add(STAT_NEW_ONEKEY_UV.ACTIVITY_ID.eq(activityId));
        }

        SelectSeekStep2 detailSelect = dslContext.select(STAT_NEW_ONEKEY_UV.CREATE_DATE.as("create_date"), STAT_NEW_ONEKEY_UV.PRODUCT_ID.as("product_id"),
                COMM_PRODUCT_PRDID.NAME.as("product_name"), STAT_NEW_ONEKEY_UV.CHANNEL.as("channel"),
                COMM_PRODUCT_CHANNEL.CHANNEL_NAME.as("channel_name"), STAT_NEW_ONEKEY_UV.PLATFORM.as("platform"),
                STAT_NEW_ONEKEY_UV.ACTIVITY_ID.as("activity_id"), STAT_NEW_ONEKEY_UV.ACTIVITY_NAME.as("activity_name"),
                STAT_NEW_ONEKEY_UV.PARTY_TYPE.as("party_type"), DSL.sum(STAT_NEW_ONEKEY_UV.UV).as("uv"))
                .from(STAT_NEW_ONEKEY_UV)
                .leftJoin(COMM_PRODUCT_PRDID).on(COMM_PRODUCT_PRDID.PRDID.eq(STAT_NEW_ONEKEY_UV.PRODUCT_ID))
                .leftJoin(COMM_PRODUCT_CHANNEL).on(COMM_PRODUCT_CHANNEL.CHANNEL_CODE.eq(STAT_NEW_ONEKEY_UV.CHANNEL))
                .where(statConditionList)
                .groupBy(STAT_NEW_ONEKEY_UV.ACTIVITY_NAME, STAT_NEW_ONEKEY_UV.CREATE_DATE)
                .orderBy(STAT_NEW_ONEKEY_UV.CREATE_DATE.asc(), STAT_NEW_ONEKEY_UV.PARTY_TYPE.asc());
        Long count = dslContext.selectCount().from(detailSelect).fetchOneInto(Long.class);
        List content = detailSelect.offset(pageable.getOffset()).limit(pageable.getPageSize()).fetch(new DetailNoviceRecordMapper());
        return new PageImpl<NoviceDetailStatResponse>(content, pageable, count == null ? 0 : count);

    }

    class DetailNoviceRecordMapper implements RecordMapper<Record, NoviceDetailStatResponse> {

        @Override
        public NoviceDetailStatResponse map(Record record) {
            NoviceDetailStatResponse response = new NoviceDetailStatResponse();
            Integer productId = record.get("product_id", Integer.class);
            String productName = record.get("product_name", String.class);
            Integer channel = record.get("channel", Integer.class);
            String channelName = record.get("channel_name", String.class);
            String platform = record.get("platform", String.class);
            Long activityId = record.get("activity_id", Long.class);
            String activityName = record.get("activity_name", String.class);
            Integer partyType = record.get("party_type", Integer.class);
            Integer uv = record.get("uv", Integer.class);
            response.setCreateDate(record.get("create_date", java.util.Date.class));
            response.setProductId(productId);
            response.setProductName(productName);
            response.setChannel(channel);
            response.setChannelName(channelName);
            response.setPlatform(platform);
            response.setActivityId(activityId);
            response.setActivityName(activityName);
            response.setPartyType(partyType);
            response.setInitiativeCount(uv == null ? 0 : uv);
            response.setOrderCount(0);
            return response;
        }
    }

    @Override
    public List<ProductInfo> getProductBySpaceId(Integer spaceId) throws Exception {

        List<ProductInfo> productInfoList = dslContext.select(LOAN_ADVERT.ACTIVITY_ID.as("id"), LOAN_PRODUCT_INFO.NAME.as("name"))
                .from(LOAN_ADVERT)
                .leftJoin(LOAN_PRODUCT_INFO)
                .on(LOAN_PRODUCT_INFO.ID.eq(LOAN_ADVERT.ACTIVITY_ID.cast(Long.class)))
                .where(LOAN_ADVERT.ADVERT_SPACE.eq(spaceId)).fetchInto(ProductInfo.class);

        return productInfoList;
    }

    public Field<String> dateFormat(Field<Timestamp> field, String format) {
        return DSL.field("date_format({0}, {1})", SQLDataType.VARCHAR,
                field);
    }

    public Field<Timestamp> date(Field<Timestamp> field) {
        return DSL.field("date({0})", SQLDataType.TIMESTAMP,
                field);
    }
}
